1 Imports System.Data.SqlClient
2 Imports Excel = Microsoft.Office.Interop.Excel
3 Public Class frmFeeStructure
4
5
6 Private Sub frmFeeStructure_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
7 fillSchool()
8 fillLocation()
9 GetData()
10 End Sub
11
12 Sub Reset()
13 cmbSchoolName.SelectedIndex = -1
14 cmbClass.SelectedIndex = -1
15 cmbSemester.SelectedIndex = -1
16 cmbClass.Enabled = False
17 cmbSemester.Enabled = False
18 GroupBox10.Visible = False
19 cmbSchoolName1.SelectedIndex = -1
20 cmbClass1.SelectedIndex = -1
21 cmbClass1.Enabled = False
22 GroupBox11.Visible = False
23 cmbLocation.SelectedIndex = -1
24 GroupBox12.Visible = False
25 dgw.Rows.Clear()
26 dgw1.Rows.Clear()
27 dgw2.Rows.Clear()
28 fillSchool()
29 fillLocation()
30 GetData()
31 End Sub
32
33 Private Sub TabControl1_Click(sender As System.Object, e As System.EventArgs) Handles TabControl1.Click
34 Reset()
35 End Sub
36
37 Private Sub Button16_Click(sender As System.Object, e As System.EventArgs) Handles btnClose.Click
38 Me.Close()
39 End Sub
40
41
42 Private Sub btnReset_Click(sender As System.Object, e As System.EventArgs) Handles btnReset.Click
43 cmbSchoolName.SelectedIndex = -1
44 cmbClass.SelectedIndex = -1
45 cmbSemester.SelectedIndex = -1
46 cmbClass.Enabled = False
47 cmbSemester.Enabled = False
48 GroupBox10.Visible = False
49 dgw.Rows.Clear()
50 End Sub
51
52 Private Sub cmbSchoolName_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles cmbSchoolName.SelectedIndexChanged
53 Try
54 cmbClass.Enabled = True
55 con = New SqlConnection(cs)
56 con.Open()
57 Dim ct As String = "SELECT distinct CourseFee.Class from CourseFee,SchoolInfo where CourseFee.SchoolID=SchoolInfo.S_ID and SchoolName=@d1"
58 cmd = New SqlCommand(ct)
59 cmd.Connection = con
60 cmd.Parameters.AddWithValue("@d1", cmbSchoolName.Text)
61 rdr = cmd.ExecuteReader()
62 cmbClass.Items.Clear()
63 While rdr.Read
64 cmbClass.Items.Add(rdr(0))
65 End While
66 con.Close()
67 Catch ex As Exception
68 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
69 End Try
70 End Sub
71
72 Private Sub cmbClass_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles cmbClass.SelectedIndexChanged
73 Try
74 cmbSemester.Enabled = True
75 con = New SqlConnection(cs)
76 con.Open()
77 Dim ct As String = "SELECT distinct Semester from CourseFee,Class,SchoolInfo where Class.Classname=CourseFee.Class and SchoolInfo.S_ID=CourseFee.SchoolID and CourseFee.Class=@d1 and SchoolName=@d2"
78 cmd = New SqlCommand(ct)
79 cmd.Connection = con
80 cmd.Parameters.AddWithValue("@d1", cmbClass.Text)
81 cmd.Parameters.AddWithValue("@d2", cmbSchoolName.Text)
82 rdr = cmd.ExecuteReader()
83 cmbSemester.Items.Clear()
84 While rdr.Read
85 cmbSemester.Items.Add(rdr(0))
86 End While
87 con.Close()
88 Catch ex As Exception
89 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
90 End Try
91 End Sub
92 Sub fillSchool()
93 Try
94 con = New SqlConnection(cs)
95 con.Open()
96 adp = New SqlDataAdapter()
97 adp.SelectCommand = New SqlCommand("SELECT RTRIM(SchoolName) FROM SchoolInfo", con)
98 ds = New DataSet("ds")
99 adp.Fill(ds)
100 dtable = ds.Tables(0)
101 cmbSchoolName.Items.Clear()
102 cmbSchoolName1.Items.Clear()
103 For Each drow As DataRow In dtable.Rows
104 cmbSchoolName.Items.Add(drow(0).ToString())
105 cmbSchoolName1.Items.Add(drow(0).ToString())
106 Next
107 Catch ex As Exception
108 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
109 End Try
110 End Sub
111 Sub fillLocation()
112 Try
113 con = New SqlConnection(cs)
114 con.Open()
115 adp = New SqlDataAdapter()
116 adp.SelectCommand = New SqlCommand("SELECT Distinct RTRIM(Location) FROM Installment_Bus", con)
117 ds = New DataSet("ds")
118 adp.Fill(ds)
119 dtable = ds.Tables(0)
120 cmbLocation.Items.Clear()
121 For Each drow As DataRow In dtable.Rows
122 cmbLocation.Items.Add(drow(0).ToString())
123 Next
124 Catch ex As Exception
125 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
126 End Try
127 End Sub
128
129 Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
130 Try
131 If Len(Trim(cmbSchoolName.Text)) = 0 Then
132 MessageBox.Show("Please select school name", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
133 cmbSchoolName.Focus()
134 Exit Sub
135 End If
136 If Len(Trim(cmbClass.Text)) = 0 Then
137 MessageBox.Show("Please select class", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
138 cmbClass.Focus()
139 Exit Sub
140 End If
141 If Len(Trim(cmbSemester.Text)) = 0 Then
142 MessageBox.Show("Please select semester", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
143 cmbSemester.Focus()
144 Exit Sub
145 End If
146 GroupBox10.Visible = True
147 con = New SqlConnection(cs)
148 con.Open()
149 cmd = New SqlCommand("SELECT RTRIM(FeeName),RTRIM(Fee) from Class,CourseFee,SchoolInfo where Class.Classname=CourseFee.Class and CourseFee.SchoolID=SchoolInfo.S_ID and SchoolName=@d1 and CourseFee.Class=@d2 and Semester=@d3 order by FeeName", con)
150 cmd.Parameters.AddWithValue("@d1", cmbSchoolName.Text)
151 cmd.Parameters.AddWithValue("@d2", cmbClass.Text)
152 cmd.Parameters.AddWithValue("@d3", cmbSemester.Text)
153 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
154 dgw.Rows.Clear()
155 While (rdr.Read() = True)
156 dgw.Rows.Add(rdr(0), rdr(1))
157 End While
158 Dim sum As Double = 0
159 For Each r As DataGridViewRow In Me.dgw.Rows
160 sum = sum + r.Cells(1).Value
161 Next
162 Dim num As Double
163 num = sum
164 num = Math.Round(num, 2)
165 txtClassFeeTotal.Text = num
166 con.Close()
167 Catch ex As Exception
168 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
169 End Try
170 End Sub
171
172 Private Sub cmbSchoolName1_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles cmbSchoolName1.SelectedIndexChanged
173 Try
174 cmbClass1.Enabled = True
175 con = New SqlConnection(cs)
176 con.Open()
177 Dim ct As String = "SELECT distinct Installment_Hostel.Class from Installment_Hostel,SchoolInfo where Installment_Hostel.SchoolID=SchoolInfo.S_ID and SchoolName=@d1"
178 cmd = New SqlCommand(ct)
179 cmd.Connection = con
180 cmd.Parameters.AddWithValue("@d1", cmbSchoolName1.Text)
181 rdr = cmd.ExecuteReader()
182 cmbClass1.Items.Clear()
183 While rdr.Read
184 cmbClass1.Items.Add(rdr(0))
185 End While
186 con.Close()
187 Catch ex As Exception
188 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
189 End Try
190 End Sub
191
192 Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
193 cmbSchoolName1.SelectedIndex = -1
194 cmbClass1.SelectedIndex = -1
195 cmbClass1.Enabled = False
196 GroupBox11.Visible = False
197 dgw1.Rows.Clear()
198 End Sub
199
200 Private Sub Button3_Click(sender As System.Object, e As System.EventArgs) Handles Button3.Click
201 Try
202 If Len(Trim(cmbSchoolName1.Text)) = 0 Then
203 MessageBox.Show("Please select school name", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
204 cmbSchoolName1.Focus()
205 Exit Sub
206 End If
207 If Len(Trim(cmbClass1.Text)) = 0 Then
208 MessageBox.Show("Please select class", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
209 cmbClass1.Focus()
210 Exit Sub
211 End If
212 GroupBox11.Visible = True
213 con = New SqlConnection(cs)
214 con.Open()
215 cmd = New SqlCommand("SELECT RTRIM(HostelName),RTRIM(Installment),RTRIM(Charges) from HostelInfo,SchoolInfo,Installment_Hostel,Class where Class.Classname=Installment_Hostel.Class and Installment_Hostel.SchoolID=SchoolInfo.S_ID and HostelInfo.HI_ID=Installment_Hostel.HostelID and SchoolName=@d1 and Installment_Hostel.Class=@d2 order by Hostelname", con)
216 cmd.Parameters.AddWithValue("@d1", cmbSchoolName1.Text)
217 cmd.Parameters.AddWithValue("@d2", cmbClass1.Text)
218 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
219 dgw1.Rows.Clear()
220 While (rdr.Read() = True)
221 dgw1.Rows.Add(rdr(0), rdr(1), rdr(2))
222 End While
223 Dim sum As Double = 0
224 For Each r As DataGridViewRow In Me.dgw1.Rows
225 sum = sum + r.Cells(2).Value
226 Next
227 Dim num As Double
228 num = sum
229 num = Math.Round(num, 2)
230 txtHostelFeeTotal.Text = num
231 con.Close()
232 Catch ex As Exception
233 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
234 End Try
235 End Sub
236
237 Private Sub Button4_Click(sender As System.Object, e As System.EventArgs) Handles Button4.Click
238 cmbLocation.SelectedIndex = -1
239 GroupBox12.Visible = False
240 dgw2.Rows.Clear()
241 End Sub
242
243 Private Sub Button5_Click(sender As System.Object, e As System.EventArgs) Handles Button5.Click
244 Try
245 If Len(Trim(cmbLocation.Text)) = 0 Then
246 MessageBox.Show("Please select location", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
247 cmbLocation.Focus()
248 Exit Sub
249 End If
250 GroupBox12.Visible = True
251 con = New SqlConnection(cs)
252 con.Open()
253 cmd = New SqlCommand("SELECT RTRIM(Location),RTRIM(Installment),RTRIM(Charges) from Installment_Bus where Location=@d1 order by installment", con)
254 cmd.Parameters.AddWithValue("@d1", cmbLocation.Text)
255 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
256 dgw2.Rows.Clear()
257 While (rdr.Read() = True)
258 dgw2.Rows.Add(rdr(0), rdr(1), rdr(2))
259 End While
260 Dim sum As Double = 0
261 For Each r As DataGridViewRow In Me.dgw2.Rows
262 sum = sum + r.Cells(2).Value
263 Next
264 Dim num As Double
265 num = sum
266 num = Math.Round(num, 2)
267 txtBusFeeTotal.Text = num
268 con.Close()
269 Catch ex As Exception
270 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
271 End Try
272 End Sub
273 Sub GetData()
274 Try
275 con = New SqlConnection(cs)
276 con.Open()
277 cmd = New SqlCommand("SELECT RTRIM(Location),RTRIM(Installment),RTRIM(Charges) from Installment_Bus order by Location,installment", con)
278 cmd.Parameters.AddWithValue("@d1", cmbLocation.Text)
279 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
280 dgw2.Rows.Clear()
281 While (rdr.Read() = True)
282 dgw2.Rows.Add(rdr(0), rdr(1), rdr(2))
283 End While
284 con.Close()
285 Catch ex As Exception
286 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
287 End Try
288 End Sub
289 End Class
